﻿using System.Web.Security;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;
using System.Data.SqlClient;


namespace RoleCustomizado
{

    public sealed class RoleProviderCustom : RoleProvider
    {
        //
        // Global connection string, generic exception message, event log info.
        //

        private string eventSource = "RoleProviderCustom";
        private string eventLog = "Application";
        private string exceptionMessage = "Ocoreu uma execeção. Por favor check o Log de Eventos.";

        private ConnectionStringSettings pConnectionStringSettings;
        private string connectionString = WebConfigurationManager.ConnectionStrings["Login"].ConnectionString;

        //
        // If false, exceptions are thrown to the caller. If true,
        // exceptions are written to the event log.
        //

        private bool pWriteExceptionsToEventLog = false;

        public bool WriteExceptionsToEventLog
        {
            get { return pWriteExceptionsToEventLog; }
            set { pWriteExceptionsToEventLog = value; }
        }

        //
        // System.Configuration.Provider.ProviderBase.Initialize Method
        //

        public override void Initialize(string name, NameValueCollection config)
        {

            //
            // Initialize values from web.config.
            //

            if (config == null)
                throw new ArgumentNullException("config");

            if (name == null || name.Length == 0)
                name = "RoleProviderCustom";

            if (String.IsNullOrEmpty(config["Descricao"]))
            {
                config.Remove("Descricao");
                config.Add("Descricao", "RoleProviderCustom Role provider Customizado");
            }

            // Initialize the abstract base class.
            base.Initialize(name, config);


            if (config["applicationName"] == null || config["applicationName"].Trim() == "")
            {
                pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
            }
            else
            {
                pApplicationName = config["applicationName"];
            }

            if (config["writeExceptionsToEventLog"] != null)
            {
                if (config["writeExceptionsToEventLog"].ToUpper() == "TRUE")
                {
                    pWriteExceptionsToEventLog = true;
                }
            }

            //
            // Initialize RoleProviderCustom.
            //

            pConnectionStringSettings = ConfigurationManager.
              ConnectionStrings[config["connectionStringName"]];

            if (pConnectionStringSettings == null || pConnectionStringSettings.ConnectionString.Trim() == "")
            {
                throw new ProviderException("Connection string Não pode ser em branco.");
            }

            connectionString = pConnectionStringSettings.ConnectionString;
        }

        //
        // System.Web.Security.RoleProvider properties.
        //

        private string pApplicationName = "SisAfast";

        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        //
        // System.Web.Security.RoleProvider methods.
        //

        //
        // RoleProvider.AddUsersToRoles
        //

        public override void AddUsersToRoles(string[] usernames, string[] rolenames)
        {
            /*  foreach (string rolename in rolenames)
              {
                  if (!RoleExists(rolename))
                  {
                      throw new ProviderException("Role não existe.");
                  }
              }

              foreach (string username in usernames)
              {
                  if (username.Contains(","))
                  {
                      throw new ArgumentException("Nomes de usuário não podem conter virgulas.");
                  }

                  foreach (string rolename in rolenames)
                  {
                      if (IsUserInRole(username, rolename))
                      {
                          throw new ProviderException("Usuário já existe nesta role.");
                      }
                  }
              }

             /*
              * SqlCommand cmd = new SqlCommand("Usp_Usuario_ObterSenhaDoUsuario", conn);

              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.AddWithValue("@Usuario", username);
              cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
              cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
            
           

              OdbcConnection conn = new OdbcConnection(connectionString);
              OdbcCommand cmd = new OdbcCommand("INSERT INTO UsersInRoles " +
                      " (Username, Rolename, ApplicationName) " +
                      " Values(?, ?, ?)", conn);

              OdbcParameter userParm = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255);
              OdbcParameter roleParm = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255);
              cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;

              OdbcTransaction tran = null;

              try
              {
                  conn.Open();
                  tran = conn.BeginTransaction();
                  cmd.Transaction = tran;

                  foreach (string username in usernames)
                  {
                      foreach (string rolename in rolenames)
                      {
                          userParm.Value = username;
                          roleParm.Value = rolename;
                          cmd.ExecuteNonQuery();
                      }
                  }

                  tran.Commit();
              }
              catch (SqlException e)
              {
                  try
                  {
                      tran.Rollback();
                  }
                  catch { }


                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "AddUsersToRoles");
                  }
                  else
                  {
                      throw e;
                  }
              }
              finally
              {
                  conn.Close();
              }*/

            throw new ProviderException("Não é Possivel AddUsersToRoles");
        }

        //
        // RoleProvider.CreateRole
        //

        public override void CreateRole(string rolename)
        {
            if (rolename.Contains(","))
            {
                throw new ArgumentException("Role não podem conter virgulas.");
            }

            if (RoleExists(rolename))
            {
                throw new ProviderException("Role já existe.");
            }

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Grupo_CriarGrupo", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Aplicacao ", ApplicationName);
            cmd.Parameters.AddWithValue("@Grupo", rolename);
           
            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "CreateRole");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
        }

        //
        // RoleProvider.DeleteRole
        //

        public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
        {
            if (!RoleExists(rolename))
            {
                throw new ProviderException("Grupo Não Existe.");
            }

            if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
            {
                throw new ProviderException("Não é possível deletar um Grupo com Dados.");
            }

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Grupo_DeletarGrupo", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DeleteOnlyIfRoleIsEmpty ", throwOnPopulatedRole);
            cmd.Parameters.AddWithValue("@Grupo ", rolename);
            cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);            

            SqlTransaction tran = null;

            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;                

                cmd.ExecuteNonQuery();                

                tran.Commit();
            }
            catch (SqlException e)
            {
                try
                {
                    tran.Rollback();
                }
                catch { }


                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "DeleteRole");

                    return false;
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            return true;
        }


        //
        // RoleProvider.GetAllRoles
        //

        public override string[] GetAllRoles()
        {
            string tmpRoleNames = "";

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Grupo_TodosOsGrupos", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Aplicacao", ApplicationName);

            SqlDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpRoleNames += reader.GetString(0) + ",";
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetAllRoles");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            if (tmpRoleNames.Length > 0)
            {
                // Remove trailing comma.
                tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
                return tmpRoleNames.Split(',');
            }

            return new string[0];
        }


        //
        // RoleProvider.GetRolesForUser
        //

        public override string[] GetRolesForUser(string username)
        {
            string tmpRoleNames = ""; 

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_GrupoXUsuario_ObterGruposPorUsuario", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Aplicacao", ApplicationName);
            cmd.Parameters.AddWithValue("@Usuario", username);

            SqlDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpRoleNames += reader.GetString(0) + ",";
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetRolesForUser");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            if (tmpRoleNames.Length > 0)
            {
                // Remove trailing comma.
                tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
                return tmpRoleNames.Split(',');
            }

            return new string[0];
        }


        //
        // RoleProvider.GetUsersInRole
        //

        public override string[] GetUsersInRole(string rolename)
        {
            string tmpUserNames = "";

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_GrupoXUsuario_ObterUsuariosPorGrupos", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Aplicacao", ApplicationName);
            cmd.Parameters.AddWithValue("@Grupo", rolename);
            
            SqlDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpUserNames += reader.GetString(0) + ",";
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUsersInRole");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            if (tmpUserNames.Length > 0)
            {
                // Remove trailing comma.
                tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
                return tmpUserNames.Split(',');
            }

            return new string[0];
        }


        //
        // RoleProvider.IsUserInRole
        //

        public override bool IsUserInRole(string username, string rolename)
        {
            bool userIsInRole = false;

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Usuario_EUsuarioDoGrupo", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Aplicacao ", ApplicationName);
            cmd.Parameters.AddWithValue("@Usuario", username);
            cmd.Parameters.AddWithValue("@Grupo", rolename);

            try
            {
                conn.Open();
                
                int numRecs;

                try
                {
                     numRecs = (int)cmd.ExecuteScalar();

                }
                catch (NullReferenceException e)
                {
                     numRecs = 0;
                }

                if (numRecs > 0)
                {
                    userIsInRole = true;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "IsUserInRole");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            return userIsInRole;
        }


        //
        // RoleProvider.RemoveUsersFromRoles
        //

        public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
        {
          /*  foreach (string rolename in rolenames)
            {
                if (!RoleExists(rolename))
                {
                    throw new ProviderException("Role name not found.");
                }
            }

            foreach (string username in usernames)
            {
                foreach (string rolename in rolenames)
                {
                    if (!IsUserInRole(username, rolename))
                    {
                        throw new ProviderException("Usuário não pertence ao grupo.");
                    }
                }
            }
            

            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("DELETE FROM UsersInRoles " +
                    " WHERE Username = ? AND Rolename = ? AND ApplicationName = ?", conn);

            OdbcParameter userParm = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255);
            OdbcParameter roleParm = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255);
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;

            OdbcTransaction tran = null;

            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;

                foreach (string username in usernames)
                {
                    foreach (string rolename in rolenames)
                    {
                        userParm.Value = username;
                        roleParm.Value = rolename;
                        cmd.ExecuteNonQuery();
                    }
                }

                tran.Commit();
            }
            catch (SqlException e)
            {
                try
                {
                    tran.Rollback();
                }
                catch { }


                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "RemoveUsersFromRoles");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }*/

            throw new ProviderException("Não é Possivel remover usuários do Grupo");
        }

        //
        // RoleProvider.RoleExists
        //

        public override bool RoleExists(string rolename)
        {
            bool exists = false;

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_Grupo_ExisteGrupo", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Aplicacao ", ApplicationName);
            cmd.Parameters.AddWithValue("@Grupo", rolename);

            try
            {
                conn.Open();

                int numRecs = (int)cmd.ExecuteScalar();

                if (numRecs > 0)
                {
                    exists = true;
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "RoleExists");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            return exists;
        }

        //
        // RoleProvider.FindUsersInRole
        //

        public override string[] FindUsersInRole(string rolename, string usernameToMatch)
        {

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Usp_GrupoXUsuario_ProcurarUsuariosNoGrupo", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Aplicacao ", ApplicationName);
            cmd.Parameters.AddWithValue("@UserNameToMatch", usernameToMatch);
            cmd.Parameters.AddWithValue("@Grupo", rolename);
                       

            string tmpUserNames = "";
            SqlDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpUserNames += reader.GetString(0) + ",";
                }
            }
            catch (SqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "FindUsersInRole");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }

                conn.Close();
            }

            if (tmpUserNames.Length > 0)
            {
                // Remove trailing comma.
                tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
                return tmpUserNames.Split(',');
            }

            return new string[0];
        }

        //
        // WriteToEventLog
        //   A helper function that writes exception detail to the event log. Exceptions
        // are written to the event log as a security measure to avoid private database
        // details from being returned to the browser. If a method does not return a status
        // or boolean indicating the action succeeded or failed, a generic exception is also 
        // thrown by the caller.
        //

        private void WriteToEventLog(Exception e, string action)
        {
            EventLog log = new EventLog();
            log.Source = eventSource;
            log.Log = eventLog;

            string message = exceptionMessage + "\n\n";
            message += "Action: " + action + "\n\n";
            message += "Exception: " + e.ToString();

            log.WriteEntry(message);
        }

    }
}